2013-01-10 - 5579.600 - Spec - ELT Reports (EPM Reports) #CrystalReportDevelopment
SPECIFICATIONS
5579.600 - ELT reports
Purpose
Design the following five EPM Reports as per NEC Requirements:
1. EPM200 - Customer Scorecard.
2. EPM210 - Customer Segment Scorecard.
3. EPM220 - License Scorecard.
4. EPM230 - Product Mix Scorecard.
5. EPM240 - Silhouette Scorecard.
Admin Info
Title
|
ELT Reports (Renamed as EPM Reports)
|
Requested By
|
Mark Harris
|
Spec Created By
|
Mallareddy D
|
Spec Created Date
|
01/10/2013
|
Spec QA by
|
Mallareddy D
|
Objects
|
EPM200, EPM210, EPM220, EPM230 and EPM240.
|
Document Status
|
Complete
|
References
Prior Tickets
5579.800
5579.900
5579.1000
Documents
EPM - mock up.pptx
Consolidation file.xlsx
EPM - layout 1-14.pdf
Consolidation file.xlsx
EPM Detail.xlsx
Contribution % TY.pdf
EPM-budget sample.pdf
Functional Requirement
03/18/2013 - Changes requested by Ashwin in EMail and Call:
1. Sub report titles should be aligned correctly.
2. Need to include Sub Report Title in each Sub report.
3. Need to Rename Last Year Variance $ to LY Var $, Budget Variance $ to Budg Var $ in Sub report.
4. Need to include Revenue % column in each Sub report prior to Actual Revenue Shipped YTD.
5. Calculation Formula Revenue % = Actual Revenue Shipped YTD/ Total Actual Revenue Shipped YTD * 100.
6. Need to include Region variable in BEx Queries.
02/28/2013 - Changes requested by Ashwin in Call:
1. For Open Values the Currency Conversion should also based on Current Period Currency Conversion Rate.
2. For Shipped Values the Currency Conversion should be based on the period and their corresponding currency conversion rate.
3. Need to show only product mix Budget values in Group1 of all EPM Scorecard reports using the Budget Data Sheet. For other groups no change.
02/26/2013 - Changes requested by Ashwin through Call:
1. For Group1 and Group2, we need to use USD values and for Group3 we need to use the currency parameter i.e. Sales Org Currency and Corporate Currency.
02/26/2013 - Changes requested by Ashwin through IM:
1. Currency Conversion - Need to include parameter to show Corporate Currency and Sales Org Currency.
2. If user selects Corporate Currency, then report shows USD. Otherwise if the user selects Sales Org Currency then report need to show the data according to the Sales Org Currency.
02/14/2013 - Changes requested by Ashwin through IM:
1. Budget data files are reduced from 5 to 1 and need to use that only in all the reports.
2. All sub reports in group 1 need to show the budget figures from budget file.
3. Need to include budget files.
02/12/2013 - Static Formulas for Budget fields provided by Ashwin in Email:
1. Budget Variance_Group1
Sum({ZMP_OPASA_ZMP_OPASA_Q001.[Measures]-[3K6K73QQITC4O7NH71NEQ0JKR]}, {@Group1}) - {@Budget_$_YTD_Group1}
2. Change this in Group 1 MTD section:
Budget Variance_Group1
Sum({ZMP_OPASA_ZMP_OPASA_Q001.[Measures]-[1EXM5HH4MKBLDG5BZ8JXQF1UJ]}, {@Group1}) - {@Budget_MTD_Group1}
2/12/2013 - Static Formulas for Budget fields provided by Ashwin in Email:
1. Create following formula in Group 1 (YTD) section:
Budget_Total_Group1
if {ZMP_OPASA_ZMP_OPASA_Q001.[0SALESORG
ZREGION]-[20SALESORGZREGION]} = '1' then 568707703
else if {ZMP_OPASA_ZMP_OPASA_Q001.[0SALESORG
ZREGION]-[20SALESORGZREGION]} = '2' then 25420290
else if {ZMP_OPASA_ZMP_OPASA_Q001.[0SALESORG
ZREGION]-[20SALESORGZREGION]} = '3' then 75557744
Budget_$_YTD_Group1
if {ZMP_OPASA_ZMP_OPASA_Q001.[0SALESORG
ZREGION]-[20SALESORGZREGION]} = '1' then 28941789
else if {ZMP_OPASA_ZMP_OPASA_Q001.[0SALESORG
ZREGION]-[20SALESORGZREGION]} = '2' then 1325906
else if {ZMP_OPASA_ZMP_OPASA_Q001.[0SALESORG
ZREGION]-[20SALESORGZREGION]} = '3' then 5220595
2. Change following formulas (Comment out the old formulas):
Budget Contribution_YTD
if {ZMP_OPASA_ZMP_OPASA_Q001.[0SALESORG
ZREGION]-[20SALESORGZREGION]} = '1' then 15025533
else if {ZMP_OPASA_ZMP_OPASA_Q001.[0SALESORG
ZREGION]-[20SALESORGZREGION]} = '2' then 899289
else if {ZMP_OPASA_ZMP_OPASA_Q001.[0SALESORG
ZREGION]-[20SALESORGZREGION]} = '3' then 3688897
Budget Contribution %_Group1
if {@Budget_$_YTD_Group1} = 0 then 0 else
[[mailto:{@Budget|{@Budget]] Contribution_YTD} / {@Budget_$_YTD_Group1} * 100
Coverage_Budget_Total_Group1
if {@Budget_Total_Group1} =0 then
0
else
Sum({ZMP_OPASA_ZMP_OPASA_Q001.[Measures]-[6CCP5PWMZR5NFA7V6ZZ0B7S4B]},{@Group1}) / {@Budget_Total_Group1} * 100
Coverage_Budget_YTD_Group1
if {@Budget_$_YTD_Group1} =0 then
0
else
Sum({ZMP_OPASA_ZMP_OPASA_Q001.[Measures]-[3K6K73QQITC4O7NH71NEQ0JKR]},{@Group1}) / {@Budget_$_YTD_Group1} * 100
3. Create following formula in Group 1 (MTD) section:
Budget_MTD_Group1
if {ZMP_OPASA_ZMP_OPASA_Q001.[0SALESORG
ZREGION]-[20SALESORGZREGION]} = '1' then 28941789
else if {ZMP_OPASA_ZMP_OPASA_Q001.[0SALESORG
ZREGION]-[20SALESORGZREGION]} = '2' then 1325906
else if {ZMP_OPASA_ZMP_OPASA_Q001.[0SALESORG
ZREGION]-[20SALESORGZREGION]} = '3' then 5220595
4. Change following formulas (Comment out the old formulas)
Budget_Contribution_CM
if {ZMP_OPASA_ZMP_OPASA_Q001.[0SALESORG
ZREGION]-[20SALESORGZREGION]} = '1' then 15025533
else if {ZMP_OPASA_ZMP_OPASA_Q001.[0SALESORG
ZREGION]-[20SALESORGZREGION]} = '2' then 899289
else if {ZMP_OPASA_ZMP_OPASA_Q001.[0SALESORG
ZREGION]-[20SALESORGZREGION]} = '3' then 3688897
Budget_Contribution_Group1
if {@Budget_MTD_Group1} = 0 then 0 else
[[mailto:{@Budget_Contribution_CM|{@Budget_Contribution_CM]]} / {@Budget_MTD_Group1} * 100
02/12/2013 - Changes requested by Mark:
I am attaching the budget numbers we should see on the MTD and Year total values. We also have to adjust the following formulas to work with this new budget info.
This is only for Group 1.
YTD section – Budget $, Budget Coverage, Budget Variance, total year Budget$, and Contribution Budget%
MTD section – Budget$ and Contribution Budget%
Here are the numbers I expect to see:
Region
|
MTD
|
Year Total
|
North America
|
28941789
|
568707703
|
EMEA
|
5220595
|
75557744
|
Emerging
|
1325906
|
25420290
|
EPM-budget sample.pdf
02/12/2013 - Changes requested by Mark:
The budgets are not being captured in the sub-reports.
Eg: On the region section of each report there is a subreport for PM, License, and Silhouette.
At this location we have a budget so it should show there.
02/07/2013 - Changes requested by Mark:
The Customer segment grouping was not fixed on the MTD section of Group 1 (region) only on the YTD section.
Customer segments should be grouped correctly in all sections of all reports.
I don’t see a footer on group 2 or group 3 pages, we put footers on all reports.
And the name of the report is ELT (in the footer) and it should be EPM200.
02/06/2013 - Changes requested by Mark:
1. Several times we’ve mentioned the format for the contribution percentage being 1 digit past the decimal. (format 99.9) and I see it in places as .99.
2. The Customer report is also missing the group 2 txt header and this is a standard and must be in every report.
3. The join in the Customer segment report is wrong and therefore causing the report to have blank pages.
02/06/2013 - Changes requested by Mark:
Check all contribution % should only be 1 digit past the decimal not 2. (format 999.9).
The second group header should be teal in color indicating drill down.
EPM200- YTD customer grouping is missing a header, the customer segments aren’t grouped correctly, and the contribution has 2 digits past decimal and should be 1.
EPM210- Customer segment join is bad.
EPM220- License scorecard is showing all and should be top 10. (I’m fixing this one).
EPM230- Product Mix (looks good but when I drill into the next level it goes back to region).
EPM240- Silhouette Looks good.
02/05/2013 - Changes requested by Ashwin through IM:
1. Initial report need to show first and second groups, but on drill down of second group third group need to see it in new tab.
2. Need to remove the Product Mix sub report in Silhouette scorecard report.
3. For Percentage figures in report, if the value is more that 999.9 that figures need to show blank.
02/05/2013 - Changes requested by Mark:
EPM 200 is showing all licenses and should only be Top 5. It also shows a customer segment of “not assigned” with 0 value. It shouldn’t be like that.
EPM 210 Shows a “not assigned” customer group again with 0 value. Bad groupings. If you look at page 13 of current report it shows YTD shipped values but nothing in product mix, customer, sil, coll.
Contribution % for TY needs to be one digit past decimal. Format of 99.9%.
EPM 220 – Can’t get it to open. HIGH priority.
EPM 230 – Customer segments are not showing correctly, or grouped correctly or something.
EPM 240 – not looked at yet.
02/04/2013 - Changes requested by Ashwin through IM:
1. For all EPM scorecard reports, Sales Org in group tree need to show descending order of Actual Revenue Shipped YTD.
02/01/2013 - Changes in layout requested by Ashwin through IM:
Change in Contribution % This Year as follows:
Contribution % This Year S = Shipped Contribution / Shipped Revenue * 100
Contribution % This Year SA = (Shipped Contribution + Open Contribution) / (Shipped Revenue + Open Revenue)
Contribution % TY.pdf
01/29/2013 - Changes requested by Ashwin through IM:
1. Silhouette scorecard need to show Top 5 and All others and Customer Scorecard need to show Top 10 and All Others.
01/28/2013 - Changes requested by Mark:
We do only need top 10 customers and all others, not ALL.
01/27/2013 - Changes reported by Mark:
Issues to be worked on:
1. All reports - Group sort will all be changed to descending year totals. (this will move NA to top of Region list)
2. Customer segment groups need some tweaking, along with data cleansing in ECC. (some customers still assigned to old groups)
3. Headers on all pages.
4. Not assigned needs to be suppressed.
5. I also see some other blank group headers that need to be looked at.
6. License report - There is a bad join on the consolidation file that only allows MLB, NBA, NFL, and a blank. We know the issue and will resolve tomorrow.
7. Product Mix - Customer needs to be only Top 5.
01/27/2013 - Block Sequences need to be used in EPM reports - Information gathered from Ashwin through IM:
1. License Scorecard: Product Mix, Customer Seg, Customer, Sil and collection.
2. Product Mix Scorecard: License, Customer Seg, Customer, Sil and collection.
3. Silhouette Scorecard: Product Mix, License, Customer Segment, Customer and collection.
4. Customer Scorecard: Product mix, customer segment, License, Sil and Collection.
5. Customer Segment Scorecard: Product Mix, License,Customer, Sil and collection.
6. In Region Group - North America need to show first so need to sort the Region Group in descending order.
7. In Product Mix Scorecard, Product Mix Group need to sort descending order of Current Revenue YTD.
01/26/2013 - Changes requested by Mark:
1. Coverage columns in top section need to be moved over.
2. TY contribution % in top section has error in formula.
3. Customer Segments need to be grouped appropriately.
4. Not assigned Sales org data should be suppressed.
5. Missed product segment subreport in MTD region section.
6. Used YTD subreport in region MTD section.
7. Missed headers on all pages.
8. Customer group 9 needs to be grouped by the first digit of the group key:
All the 100s together, all the 200s together
1 - Tastemakers: 100 Tastemakers.
2 - Goldstar/DTC: 200 Goldstar,201 DTC,202 Premium Department Store.
3 - Fashion, Regional, & Ind: 300 Surf/Skate/Snow, 301 Lifestyle Specialty, 302 Footwear Specialty, 303 Headwear Specialty, 304 Kids Specialty and 305 Better Department Store.
4 - National Key Accounts: 400 National Key Accounts and 401 Company Store.
5 - Sporting Goods: 500 Athletic Specialty and 501 Sporting Goods.
6 - Fan Specialt: 600 Fan Specialty, 601 Internet/Catalog, 602 Mid-tier Department Store and 603 Gift Airport.
7 - Concessionaire Statium Environment: 700 Bookstore, 701 Concessionaire and 702 Pro Teams.
8 - Mass: 800 Club/Mass/Discount.
9 - Other: 900 Collaboration, 901 Team Equipment, 902 Promotional, 903 Corporate, 904 Off price and 905 Other.
EPM Detail.xlsx
01/25/2013 - Changes requested from Ashwin through IM:
1. Last Year Total = Last Year Shipped earlier it is Last Year Total = Total Open Last Year + Last Year Shipped.
01/23/2013 - Changes requested from Ashwin through IM:
1. Customer Group7, Customer Group8 and Customer Group9 will handle in ECC itself in Customer Group9 object. So Customer Segement = Customer Group 9 only.
2. Need to Restrict the Point of Purchase for Material Group in BEx.
3. All Budget Values in reports need to show blank if it is Zero and other values need to show zero if it is zero but not blank.
01/22/2013 - Changes requested by Ashwin through IM:
1. From now onward the following characters need to used as Navigational instead of Transactions in all reports:
SBU, License, Material Group, Team, Silhouette, Cust Grp 7 to 10, Region, Logo, Gender and Sport.
01/21/2013 - Changes requested by Ashwin through IM:
1. So far Budget file has Budget Revenue only, need to include Budget Contribution and Budget Quantity also in Budget sheet.
2. Budget Quantity and Budget Contribution in sheet shows Zero for now.
3. Variance figures need to show numbers but not %.
01/19/2013 - Changes requested by Ashwin through IM:
1. In report Customer segment - used the Customer Group 8 infoobject, now the customer segment means combinations of Customer Group7, Customer Group 8, and Customer Group 9.
01/18/2013 - Changes requested by Ashwin through IM:
1. So far the report is developed using CR 2011 in BO 4.0 Environment. Due the the BO Migration postponed the report need to develop using CR 2008 in BO 3.1 Environment.
01/17/2013 - New Requirment requested by Ashwin through IM:
1. Month Parameter in report, Lower limit should be defaulted to 1 and should not be visible to the user who run the report and the user need to see the upper limit which ever the value he input the YTD values need to fetch
upto the month values input by the user.
01/16/2013 - New Requirement requested by Ashwin:
Consolidation file.xlsx
01/16/2013 - Requirements received from Ashwin through IM:
1. The boxes in sub reports should be dynamic not static.
2. Need to include the Top N parameters for Customers, Licenses, Silhouettes and Collection in report.
3. Open Revenue YTD = Total Open Revenue Last Year + Total Open Revenue This Year.
4. Contribution % Last Year = Shipped Contribution Last Year / Shipped Revenue Last Year * 100.
5. Open Revenue MTD = Open Revenue for the selected Month.
6. Open Contribution MTD = Open Contribution for the selected Month.
7. Month Parameter used in report should accept single value, not range of values.
The ELT reports shows:
1. Shipped Revenue, Open Revenue, Total Revenue, Last Year Revenue, Budget Revenue, LY Variance Revenue, Budget Variance Revenue, TY Contribution, LY Contribution, Budget Contribution, TY Quantity, LY Quantity and
Budget Quantity.
The report needs the Period and Year parameters.
Reports Grouping: Region > License > Sales Org.
Reports Ordering: Product mix > Customer Segment > Customer > Silhouette and Collection.
Linking: Fields of 'Region > Sales Org > License' from main report to groups of 'Region > Sales Org > License' in sub report.
Need to link Consolidation & Budget files.
01/15/2013 - Report changes requested by Mark:
New layout for the EPM reports.
1. Layout should match what I have attached. Arial font.
2. Where we have Budget identified we should have a number that matches to our budget control file and if they don’t have a number it should be 0.
3. The grouping has changed to a whole section on Year then sub groups, and then Month with subgroups. Please look and review.
4. Please make the box size dynamic, so that if there are only 3 items the box is tight to the items. And if there are 6 it expands.
5. We need to have a parameter for Top N of each group.
Eg: If they want to see top 5 licenses, then we have a parameter that we enter 5, run report and 5 show. Want to see 3 we can change it to 3. Make sense? We already use this functionality in SA210 with Top N customers.
EPM - layout 1-14.pdf
Consolidation file.xlsx
01/8/2013 - Initial Requirement received by Mark:
1. Need Current Year: Shipped Revenue, Open Revenue, Total Revenue, Last Year Revenue and Budget.
2. Need Current Month: Shipped Revenue, Open Revenue, Total Revenue, Last Year Revenue and Budget.
3. Need TY Ontime - Fill Rate - Ontime - Fill Rate - First Pass.
4. Need CY Ontime - Fill Rate - Ontime - Fill Rate - First Pass.
5. Need to include Product Mix, Customer Segment, Silhouette, Customer and Collection Subreports.
6. Each subreport need to show Shipped Revenue, Shipped Contribution $, Open Revenue, Open Contribution $, Open Contribution %, Total Revenue, Last Year Revenue and Budget Revenue.
EPM - mock up.pptx
Solution Summary
These reports are going to be designed using SAP BEx Queries and Excel files.
Test Plan
SNo
|
Test Scenario
|
Expected Results
|
1
|
Crystal Reports Values need to be validated against BEx queries
|
Values Should be identical
|
Solution Details
These reports are going to be designed using SAP BEx queries to fetch Shipped Revenue, Open Revenue, Total Revenue, Last Year Revenue, Budget Revenue,LY Variance Revenue, Budget Variance Revenue, TY Contribution, LY Contribution,Budget Contribution,TY Quantity, LY Quantity, Budget Quantity.
And Excel files are used to fetch Budget Revenue and also used the consolidation files to get the descriptions for License, Silhouette, Collection and Customer Segment defined by New Era.
Issues
None